Intermediate PHP and MySQL

Introduction

Hello, and welcome to Intermediate PHP and MySQL. My name is Rich Blum, and I'll be your instructor for this course. Over the next six weeks, we're going to build on your knowledge of PHP and MySQL so that you can create Web applications that are more professional and interactive.

If you're taking this course, you should already know the basics of using PHP code in standard HTML Web pages to produce dynamic content. You should also be comfortable working with the PHP code required to store and retrieve data in a MySQL database server.

Now, before we dive in to our subject, let me tell you a little bit about myself. (This will be familiar to you if you took my Introduction to PHP and MySQL course.) Since 1988, I've been a network and server support person at a large (3500+ user) organization. For almost two decades I've worked with Windows- and Linux-based network servers and desktop computers.

My PHP experience comes from creating dynamic, interactive Web pages for network monitoring. To help manage the chaos on our network, I created my own network monitoring applications using Java and the MySQL database on a Linux server. To make it easy to extract real-time data from the database, I created a Web-based interactive reporting system. After trying out a few different technologies, I settled on using PHP to create fancy Web interfaces. I'm excited to pass what I know on to you!

In this course, we'll use a single project to explore advanced PHP and MySQL concepts. The project we'll work on is a fully-functional online food store application. We'll examine the elements required to allow customers to shop online, select items for purchase, and of course, check out. We'll also cover the functions required to run the online store, and all the administration work required to maintain the product catalog, monitor stock levels, and process customer orders.

Along the way, you'll get a recap of the array data type in PHP and see how to use it to store your customers' shopping cart information. You'll also discover how to create your own PHP functions to simplify your life. Using functions, you will only have to code long processes once, and then you can use them anywhere in your applications!

As with any content management system, the database is key to the success of our online store project. So, we'll look at some more advanced features the MySQL database server provides. You'll see how you can store and retrieve binary data, such as pictures and audio and video clips, directly in your database tables. We'll also use the SQL database language to perform some advanced queries and data manipulation for our application.

As we build our food store project you'll learn how to create the administration section of the application, building a Web interface to allow your store manager to add new products and modify existing ones. After you get a few products stored in your database, we'll turn our attention to the customer side of things.

When we work on the customer interface, we'll focus on the PHP and MySQL code required to implement online shopping. You'll see how commercial sites implement shopping carts, and you'll duplicate those features. You'll allow your customers to browse through the online catalog and place items in their own shopping cart. Of course, a shopping cart is useless unless you can actually purchase the items you place in it. So we'll go over the Web pages required to implement a customer check-out system, complete with registering users on your system, and creating orders for processing.

And once we get all that done, we'll turn our attention back to the administrative side of things. After customers submit orders, the store manager must be able to process them. So we'll look at some fancy MySQL techniques for handling orders, including how to automatically update data in one table from data entered into another table.

That's a lot of things to cover in just six weeks, but we'll take it one step at a time.

Today we're going to work on getting back up to speed with the PHP and MySQL environment. We'll discuss the AMP server, how it processes PHP code, and what configuration items we need so we can use advanced PHP features. We'll quickly review some coding features you already know and then discuss a few more PHP functions available to work with the MySQL database. And finally, we'll walk through all of the pieces we'll need for our food store project.

Okay, let's move on to Chapter 2 and start off by taking a closer look at our AMP server.

Re-introducing AMP

Before you can start working on your PHP project, you'll need a development environment in which to build and test your programs. The main platform for hosting PHP applications is AMP (an Apache Web server, a MySQL database server, and the PHP programming language module).

If you took my introductory course, you've already used the popular WampServer for Windows PCs and servers. This is a great platform for any type of PHP and MySQL work. So we'll use it for this course. If you don't have the WampServer installed, you can easily install it by following the instructions shown in the Supplementary Material for this lesson.

You should be familiar with running a Web project using all of the defaults as set by the WampServer developers. But let's take a look at what's under the hood in the WampServer settings. This will not only give you a better understanding of how WampServer is configured, but it will also give you a better understanding of the requirements for any Apache server running PHP.

PHP Configuration

Since PHP is a server-side scripting program, the Apache Web server must know how to process all of the PHP code you embed in your Web pages before it sends the page off to the client's browser. The key to this process is that the Apache server must incorporate a module for handling PHP code (this is not a default feature in the Apache Web server). The PHP module plugs into the default Apache Web server via configuration in the Apache httpd.conf configuration file. Fortunately, the WampServer already does this work for you.

The default Apache PHP module processes standard PHP statements and functions it finds in the Web page. The Apache server sends any code you enter between the <?php and ?> tags in your HTML document through the PHP preprocessor module before sending it to the client Web browser.

The standard PHP language provides lots of functions for performing normal programming work. However, as the PHP language became more popular, the PHP developers found themselves adding lots more functions. Functions for interfacing with specific external servers, such as the MySQL server, were becoming popular among PHP users. Unfortunately, as the developers added new features to PHP, it became somewhat of a huge application.

It didn't take long before the PHP developers realized they were heading for trouble. There was no way they could include all of the specialized functions into a single PHP package. Fortunately for us, they came up with a simple solution.

PHP Extensions

Instead of trying to include all of the specialized functions in the core PHP package, the developers created extensions. Extensions are simple external library files that contain specialized PHP functions that you can easily include in the core PHP package. PHP users and developers around the world create new extensions for all kinds of specialized functions. Instead of having one huge PHP library, you only need to include the library files for the specialized functions you plan on using in your programs.

After creating extensions, however, PHP developers had a different problem on their hands: how to keep track of all the new extensions people were developing. The answer is the PHP Extension Community Library (PECL—pronounced pickle).

PECL is a central clearinghouse for all PHP extensions. You can find specialized extensions for almost any function you want to implement in your PHP code. Since it is a true Open Source system, developers store PHP extensions in PECL as complete source code. Normally, you must compile the individual PECL PHP extensions into a library file for your specific development environment. However, WampServer comes to our rescue yet again.

WampServer and PECL

The WampServer incorporates full support for PECL extensions (that's how we get our MySQL database functions). In fact, the WampServer developers were even nice enough to include quite a large set of PECL PHP extensions with the package to provide quite a robust PHP development environment for us!

WampServer stores the PHP extensions in the c:\wamp\bin\php\php5.2.5\ext folder by default. (WampServer keeps all of the PHP files under the c:\wamp\bin\php folder.) Looking there, you'll notice that all of the extensions are standard Windows .dll library files. The PHP preprocessor calls the PHP functions directly from the required .dll library file.

You can see a list of all the PHP extensions included in WampServer by following these steps:

You should see a long list of PHP extensions, as shown here.

The WampServer PHP extensions listing

You'll notice that some of the extensions have a marker next to them. The marker indicates that the specific extension has been activated in the WampServer configuration. While WampServer includes library files for all of these extensions, it doesn't activate them by default so as to cut down on the library size for the PHP preprocessor. As you feel you need to use additional extensions, just click the extension name to activate it.

Available PHP Extensions

You can easily recognize some of these extensions by their name (such as the php_mysql extension). Others are not quite so easy to identify. The following table shows some of the more popular PHP extensions available in WampServer.

In the WampServer, enabling each of these extensions is as easy as selecting the entry in the PHP extensions listing. However, if you're using the AMP server on your ISP's host, you won't be able to do that.

The php.ini configuration file contains the settings for which extension libraries PHP loads. To enable an extension, just enter a line that defines the extension library:

extension=php_mysql.dll

This configuration tells the PHP preprocessor which library files to load at start-up. If you change any of these settings, you'll need to stop and restart the Apache Web server.

Now let's move on to Chapter 3, where we'll do a quick PHP review.

The PHP Basics

Let's do a quick recap of what you should already know about PHP.

Using PHP Code

You have to identify your PHP code in the HTML documents so the Apache Web server knows to process it. You do this using the <?php and ?> tag combination:

It's also important to remember that you need to save any Web pages that incorporate PHP code using the .php file extension. Thus, follow these steps to test this code:

1. Use a standard text editor to create a file called test.php in the WampServer www folder

Note: If you're using Microsoft Notepad to create your PHP code files, remember to use double quotes around the filename ("test.php") when in the Save or Save As dialog box. Otherwise Notepad adds the .txt extension to the filename, causing all sorts of problems!

2. Copy the example code shown above into the test.php file and save it in the c:\wamp\www folder (if you chose another default documents folder when installing WampServer, save the file there instead).
3. Start the WampServer by clicking Start > Programs > WampServer > start WampServer.
4. Open a browser, and connect to the URL http://localhost/test.php.

If all is well, you'll see the following in your browser window.

The test.php code in the Internet Explorer browser

If this is working, you're ready to move on to the course project. If not, stop by the Lesson 1 Discussion Area. I'll be around to help you troubleshoot any problems you have with your WampServer development environment.

Connecting to MySQL

The key to your content management system is the MySQL database server. The WampServer includes a default setup for MySQL (including the PHP MySQL extensions). This enables you to access your MySQL databases and tables from your PHP code.

There are a few different PHP functions for interacting with your database. To start out, you need to connect to your database server using a user ID and password, and then select the database for your project:

$con = mysql_connect("localhost", "test", "test") or die('Could not connect to the server');
mysql_select_db("recipe", $con) or die('Could not connect to the database');

These two simple statements carry a big punch! They're the core behind your entire content management code. First, you use the mysql_connect function to connect to the server. In your development environment, the MySQL server is on the same host as the Apache server. So you use the special localhost host name. If this is different in your development environment, you'll have to place the specific host name for your MySQL server.

After connecting to the MySQL server, you need to select a default database for all of your SQL queries. You use the mysql_select_db function just for that purpose.

Once you're connected to the database, you can go to town sending SQL queries and analyzing the results. First, create a string variable that contains the SQL statement. Then use the mysql_query function to send the query to the MySQL server:

$query = "SELECT recipeid, title, shortdesc, poster FROM recipes ORDER BY recipeid DESC LIMIT 0,5";
$result = mysql_query($query);

Remember that SQL is the programming language the MySQL server uses to extract data stored in the database. Often, creating the correct SQL statements is just as (if not more) important as creating the PHP code.

After you send the query to the MySQL server, you can extract and analyze the response. The MySQL server returns its response in a result set. The result set contains records or information related to the query you submitted. If you expect the MySQL server to return data as a result of the query (as is the case from our SELECT SQL query), you can iterate through the result set using the mysql_fetch_array() function:

while($row=mysql_fetch_array($result, MYSQL_ASSOC))
{
$recipeid = $row ['recipeid'] ;
$title = $row ['title'] ;
$poster = $row ['poster'] ;
$shortdesc = $row ['shortdesc'] ;
echo "<a href=\"index.php?content=showrecipe&id=$recipeid\">$title</a> submitted by $poster<br>\n";
echo"$shortdesc<br><br>\n";
}

The mysql_fetch_array() function returns one record from the result set at a time. PHP places the returned data in an associative array. The associative array uses the data fields as the array keys and the data field values as the array values. You extract the result set values, place them in PHP variables, and finally, use the variables in an echo statement to create an HTML link on your Web page using your dynamic database data.

Additional PHP MySQL Functions

While you probably know the basic MySQL functions available to produce dynamic Web pages, there are a host of other functions available to you to use in your programming. The following table lists some of the more popular MySQL functions.

As you can see, there's a wealth of information you can obtain from your PHP program without having to use the MySQL Console or phpMyAdmin programs!

All right, that's enough review for now. It's time to talk about our project.

The Course Project

As we discussed in Chapter 1, in this course, we're going to build a complete Web application as a hands-on way to learn advanced topics in PHP and in MySQL. The project is called the Food Store. It's an online store operation that allows visitors to browse through a catalog of products, add items to a shopping cart, and check out when they're finished.

Before we dive into coding the project, let's take a quick tour of what to expect.

The Food Store Storefront

The core of any online store is the storefront. This is the main Web page customers use to browse through products, purchase products, and check out. It's no secret that popular commercial online Web stores have spent thousands (if not millions) of dollars perfecting the online shopping experience. There's no way that we can match that with our little course project. However, that said, there's no reason we can't copy some of their functionality in our own project.

Our Food Store project incorporates many features of a standard online store—just not quite as fancy! When a customer connects to our storefront, he or she is greeted with our main Web page.

The Food Store main Web page

We'll be using the idea of dividing our main Web page into table sections and using PHP include() functions to incorporate standard elements into our page. The sections in our storefront are:

• The header section
• The footer section
• The navigation section
• The main area section
• The shopping cart section

The main area contains our individual Web pages. When the customer selects a section of the catalog to browse, it'll show a list of the products.

The Food Store product catalog area

As the customer places items in the shopping cart (yes, we'll use the shopping cart technology in our project), the updated shopping cart appears in the left-side status area. This gives the customer a real-time view of what items are in the cart. We'll use a session cookie to store the shopping cart information during the customer's Web session.

When our customer decides it's time to check out, we provide a Web page that shows the details of the shopping cart and allows him or her to modify any items.

Modifying an item in the shopping cart

After the items are to the customer's liking, we offer a checkout area.

The checkout registration page

First, we allow the customer to either create a new account or log in using an existing registration account. After the customer logs in, he or she can finalize the order. The application shows a confirmation page, which the customer can print.

The order confirmation page

The Administrative Side

Obviously, you can't run an online store with just a storefront. Several tasks need to happen behind the scenes. So the project includes the Web pages required for managerial functions. Of course, the first thing the manager will need to do is log in to the system (you only want people changing things who are authorized to do so).

The Food Store admin login page

Once the manager logs in to the system, the administrative pages use the same layout as the storefront pages. The left side has a navigation area, allowing the manager to select between different managerial functions. The right side shows updated status information, including the number of products in the store, the number of products that are out of stock, and the number of orders waiting to be processed.

The Browse Products page

If the manager needs to edit product information (which can include uploading a new image of the product), he or she can just click the product link, and the Edit Product page appears.

The Edit Product page

Similarly, the manager can add new products, and even new product categories, using easy HTML forms to enter the new information.

Finally, one of the most important parts of the application is the ability for the manager to approve orders. The Food Store application provides an easy interface for the manager to list all pending orders and process an individual order.

Processing a pending order

As you can see, there are lots of Web pages for us to create in this application. We'll be pretty busy over the next few weeks, but I think that's enough for now. Follow me to the next chapter to wrap up this lesson.

Summary

In this lesson, we discussed what you'll need to develop the course online store project. For the development environment, you'll need an AMP server. The WampServer provides an easy way to host PHP and MySQL projects directly on your Windows PC or server. The WampServer even allows you to customize your PHP environment by including PHP extensions. PHP extensions provide additional functionality to the core PHP programming environment. We need to use PHP extensions to connect to the MySQL database server.

Next, we talked about the basics of using PHP to connect to a MySQL database server, how to send SQL queries to the server, and how to extract the result set that the MySQL server returns. There are lots of MySQL functions available in the PHP MySQL extension. We covered how to use the core functions and looked at a list of MySQL functions that might come in handy while you work on projects.

Finally, we examined the layout of the course project.

In Lesson 2, we'll start looking at the advanced PHP features you'll need to implement in the Food Store project. You'll see how we can store the customer's shopping cart information in an array session cookie and how to write custom PHP functions that'll save you from lots of typing in later code files.

Next Steps
	After every lesson in this course, you can test your knowledge by taking a short, multiple-choice quiz. To access your first quiz, click the word Quizzes at the top or bottom of any page in this classroom. When the quiz form comes up, select Lesson 1 Quiz. Once you've taken the quiz, apply what you've learned by doing this lesson's assignment. While the assignments that accompany every lesson in this course are optional, they will reinforce and expand your learning. To access your first assignment, click Assignments at the top or bottom of any page in the classroom. Then, scroll down to the section for Lesson 1. Also, since learning something new usually raises questions, every lesson in this course comes with an FAQ section. You'll find the link that goes to this lesson's Frequently Asked Questions by clicking the Resources link at the top or bottom of any page in this classroom. Within the Resources link, you'll also find the course index, which many students like to print and use as a detailed table of contents. Also, you'll find book recommendations that I've found very helpful and would like to pass along to you. These aren't required, but they're excellent sources for further learning.